package com.xtedu.examproject.dao.impl;

import com.xtedu.examproject.dao.base.MenuDao;
import com.xtedu.examproject.entity.Menu;
import com.xtedu.examproject.util.DBUtil;
import com.xtedu.examproject.util.PageHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MenuDaoImpl implements MenuDao {
    @Override
    public List<Menu> findMenusByRoleId(int roleId) throws Exception {
        //获取登录角色的菜单：通过role_id执行不同的查询语句
        //获取role_id
        String sql;
        List<Menu> menus = new ArrayList<>();
        sql = "select rm.role_id,mi.* from role_menu rm left join menu_items mi on rm.menu_id=mi.id where mi.state=1 and rm.role_id=? ";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, roleId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Menu menu = new Menu();
                menu.setId(rs.getInt("id"));
                menu.setPid(rs.getInt("pid"));
                menu.setName(rs.getString("name"));
                menu.setPath(rs.getString("path"));
                menu.setIcon(rs.getString("icon"));
                menu.setState(rs.getBoolean("state"));
                menus.add(menu);
            }
        }
        return menus;
    }

    @Override
    public int insert(Menu menu) throws Exception {
        String sql = "insert into menu_items (pid,name,path,icon,state) values(?,?,?,?,?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, menu.getPid());
            ps.setString(2, menu.getName());
            ps.setString(3, menu.getPath());
            ps.setString(4, menu.getIcon());
            ps.setBoolean(5, menu.isState());
            return ps.executeUpdate();
        }
    }

    @Override
    public int update(Menu menu) throws Exception {
        String sql = "update menu_items set pid=?,name=?,path=?,icon=?,state=? where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, menu.getPid());
            ps.setString(2, menu.getName());
            ps.setString(3, menu.getPath());
            ps.setString(4, menu.getIcon());
            ps.setBoolean(5, menu.isState());
            ps.setInt(6, menu.getId());
            return ps.executeUpdate();
        }
    }

    @Override
    public int delete(Menu menu) throws Exception {
        String sql = "delete from menu_items where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, menu.getId());
            return ps.executeUpdate();
        }
    }

    @Override
    public int deleteById(int id) throws Exception {
        return 0;
    }

    @Override
    public Menu findById(int id) throws Exception {
        String sql = "select * from menu_items where id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            Menu menu = new Menu();
            if (rs.next()) {
                menu.setId(rs.getInt("id"));
                menu.setPid(rs.getInt("pid"));
                menu.setName(rs.getString("name"));
                menu.setPath(rs.getString("path"));
                menu.setIcon(rs.getString("icon"));
                menu.setState(rs.getBoolean("state"));
            }
            return menu;
        }
    }

    @Override
    public List<Menu> findAll() throws Exception {
        String sql = "select * from menu_items";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            List<Menu> menus = new ArrayList<>();
            while (rs.next()) {
                Menu menu = new Menu();
                menu.setId(rs.getInt("id"));
                menu.setPid(rs.getInt("pid"));
                menu.setName(rs.getString("name"));
                menu.setPath(rs.getString("path"));
                menu.setIcon(rs.getString("icon"));
                menu.setState(rs.getBoolean("state"));
                menus.add(menu);
            }
            return menus;
        }
    }

    @Override
    public PageHelper findAllByPage(int pageindex, int pagesize) throws Exception {
        return null;
    }

    @Override
    public PageHelper findPageByRoleIds(int pageindex, int pagesize, int... roleIds) throws Exception {
        return null;
    }

    @Override
    public int findIdByNameAndPath(String name, String path) throws SQLException {
        int newId = 0;
        String sql = "select id from menu_items where name=? and path=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setString(2, path);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                newId = rs.getInt("id");//获取id
            }
        }
        return newId;
    }

}
